Modern enterprise database warehouses are generally deployed using web-based multi-tier architecture (often referred to as n-tier architecture). N-tier architecture is a client-server architecture in which an application is executed by more than one distinct software agents. For example, an application that uses middleware to service data requests between a user and a database employs multi-tier architecture.
The most widespread use of “multi-tier architecture” refers to three-tier architecture, which includes a presentation tier, a middle tier and a database tier. Typically each tier is developed and maintained as independent modules, most often on separate platforms.
Typically, the presentation tier, in the form of a user interface, runs on a desktop PC or workstation and uses a standard graphical user interface. The middle tier, in the form of functional process logic, may consist of one or more separate modules running on a workstation or application server. The database tier, in the form of a relational database management system (“RDBMS”), on a database server or mainframe contains the data storage logic. It will be appreciated that anyone of these tiers may be multi-tiered itself. The Teradata Demand Chain Management (“DCM”) Solution is an example of one such system.
In systems, such as the Teradata DCM solution, a typical user uses the presentation tier via web pages to request data from the database. The presentation tier interacts with the middle tier, typically, by using Microsoft N-tier ASP/COM technologies. The middle tier contains business components and logic to assemble SQL queries corresponding to the user's request from the presentation tier. The database tier receives the SQL query generated by business tier and interacts with the RDBMS to execute the SQL query. The result set of the query is sent back to the middle tier, where it is rearranged and formatted. Once the middle tier is done processing the result set, the portion of the results that can fit on a single web page is sent back to the user.
It is not at all uncommon for a result set to contain thousands or hundreds of thousands of rows of data, however, a typical web page may only accommodate 25 to 50 rows. The common solution to this is to page the result set, which is a feature enabled by Microsoft's ADO and .NET platform. The problem with paging in this manner is that a huge chunk of unnecessary data (i.e. the entire result set specified by the SQL query) travels via the network from the database to the application server, before it can be paged. Moreover, a complete record set (containing all the data) is sent to the application server each time a new page is viewed. This has an impact on the application server memory and the bandwidth between the web server and the database server (if they are on separate machines).
It is an object of the present invention to provide a new and useful system and method for returning results from a database query.